import pickle
import traceback
import pymysql


# 数据库配置
host = '127.0.0.1'
user = 'fwq'
password = '123456'
database = 'exam'
charset = 'utf8'

#插入paper——manage
def insertMaterial(question,ans,score,level):
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset,
        autocommit=True,  # 自动提交事务
    )
    cursor = conn.cursor()

    # 插入 SQL 语句
    sql = """
    INSERT INTO  material_question  
    (question,answer,score,level) 
    VALUES (%s,%s,%s,%s)
    """
    # 插入的参数
    parm =( question,ans,score,level)

    # 执行插入操作
    try:
        cursor.execute(sql, parm)
        conn.commit()  # 提交事务
        print("数据插入成功！")
    except Exception as e:
        print("发生异常：", e)  # 打印异常信息
        print("异常详情：")
        print(traceback.format_exc())  # 打印完整的异常堆栈信息
        conn.rollback()  # 回滚事务
    finally:
        # 关闭连接
        cursor.close()
        conn.close()


#插入paper——manage
def insertPaper(paperId,type,questionId):
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset,
        autocommit=True,  # 自动提交事务
    )
    cursor = conn.cursor()

    # 插入 SQL 语句
    sql = """
    INSERT INTO  paper_manage  
    (paperId,questionType,questionId) 
    VALUES (%s, %s,%s)
    """
    # 插入的参数
    parm = (paperId,type,questionId)

    # 执行插入操作
    try:
        cursor.execute(sql, parm)
        conn.commit()  # 提交事务
        print("数据插入成功！")
    except Exception as e:
        print("发生异常：", e)  # 打印异常信息
        print("异常详情：")
        print(traceback.format_exc())  # 打印完整的异常堆栈信息
        conn.rollback()  # 回滚事务
    finally:
        # 关闭连接
        cursor.close()
        conn.close()




#插入试卷
def insertExam(type,description,source,examDate,totalTime,totalScore,tips,sid,paperId):
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset,
        autocommit=True,  # 自动提交事务
    )
    cursor = conn.cursor()

    # 插入 SQL 语句
    sql = """
    INSERT INTO  exam_manage  
    (type,description,source,examDate,totalTime,totalScore,tips,studentId,paperId) 
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)
    """
    # 插入的参数
    parm = (type,description,source,examDate,totalTime,totalScore,tips,sid,paperId)

    try:
        cursor.execute(sql, parm)
        conn.commit()  # 提交事务
        print("数据插入成功！")
    except Exception as e:
        print("发生异常：", e)  # 打印异常信息
        print("异常详情：")
        print(traceback.format_exc())  # 打印完整的异常堆栈信息
        conn.rollback()  # 回滚事务
    finally:
        # 关闭连接
        cursor.close()
        conn.close()

#插入选择题
def insertChoice(question, answerA, answerB, answerC, answerD, rightAnswer,score,level) :
    # 连接数据库
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset,
        autocommit=True,  # 自动提交事务
    )
    cursor = conn.cursor()

    # 插入 SQL 语句
    sql = """
    INSERT INTO multi_question 
    (question, answerA, answerB, answerC, answerD, rightAnswer,score,level) 
    VALUES (%s, %s, %s, %s, %s, %s, %s,%s)
    """
    # 插入的参数
    parm = (question, answerA, answerB, answerC, answerD, rightAnswer,score,level)

    # 执行插入操作
    try:
        cursor.execute(sql, parm)
        conn.commit()  # 提交事务
        print("数据插入成功！")
    except Exception as e:
        print("发生异常：", e)  # 打印异常信息
        print("异常详情：")
        print(traceback.format_exc())  # 打印完整的异常堆栈信息
        conn.rollback()  # 回滚事务
    finally:
        # 关闭连接
        cursor.close()
        conn.close()

#查询表中第一个主键，Idname主键名，tableName表名
def searchId(IdName,TableName):
    # 连接数据库
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset,
        autocommit=True,  # 自动提交事务
    )
    cursor = conn.cursor()

    # 插入 SQL 语句
    sql = f"SELECT {IdName} FROM {TableName} ORDER BY {IdName} DESC LIMIT 1;"

    # 执行插入操作
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        conn.commit()  # 提交事务
        print("操作成功！")
    except Exception as e:
        print("发生异常：", e)  # 打印异常信息
        print("异常详情：")
        print(traceback.format_exc())  # 打印完整的异常堆栈信息
        conn.rollback()  # 回滚事务
    finally:
        # 关闭连接
        cursor.close()
        conn.close()
    #获得结果
    if result:
        return int(result[0])
    else:
        return 0
#查询所有学生的学号
def searchStudentId():
    # 连接数据库
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset,
        autocommit=True,  # 自动提交事务
    )
    cursor = conn.cursor()

    # 插入 SQL 语句
    sql = "SELECT studentId FROM student"

    # 执行插入操作
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        conn.commit()  # 提交事务
        print("操作成功！")
    except Exception as e:
        print("发生异常：", e)  # 打印异常信息
        print("异常详情：")
        print(traceback.format_exc())  # 打印完整的异常堆栈信息
        conn.rollback()  # 回滚事务
    finally:
        # 关闭连接
        cursor.close()
        conn.close()
    #获得结果

    ids = [row[0] for row in result]
    return ids

#为每个学生生成试卷，插入paper

# 测试插入
if __name__ == "__main__":
    searchStudentId()
